﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class JLTVK02YFData : BaseFunction
    {
        
        public JLTVK02YFData()
            : base()
        {

        }
        public JLTVK02YFData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetVK02(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WASTAMM";
            string DESCR = dwABPOS["DESCR"].ToString().Trim();
            string FROMDNDATE = dwABPOS["FROMDNDATE"].ToString().Trim();
            string ENDDNDATE = dwABPOS["ENDDNDATE"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = " SELECT * FROM (SELECT A.VKPPOSTYP,(select substr(sddaten, 2, 20) from sddat "
                    + " where sdsa = '0253' and substr(sdkey, 24, 1) = 'V' and substr(sddaten, 1, 1) = 'A' "
                    + " and substr(sdkey, 3, 3) = A.VKPPOSTYP) AS TYPE, "
                    + " A.VKPNEUDAT,to_number(A.VKPKUNDNR) as customer,trim(B.KDKNAME) as cuname, "
                    + " A.VKPAUFKRS,TO_NUMBER(A.VKPAUFLFD) AS ORDERNO,to_number(A.VKPAUFPOS) AS ITEM, "
                    + " NVL((SELECT MAX(TRIM(T2.TXTKTEXT)) AS YARN from txtind T1, TXTKON T2 "
                    + " where T1.txtitxtart = 'VAD' AND T1.TXTIMANDANT = T2.TXTKMANDANT "
                    + " AND T1.TXTIPIND = T2.TXTKPIND AND A.VKPKOLL || TRIM(A.VKPART) = TRIM(T1.TXTISIND)), "
                    + " D.VAABEZ)AS DESCR,D.VAAARTGRP AS ARTGRP, "
                    + " (select substr(sddaten, 2, 20) from sddat where sdsa = '0178' "
                    + " and substr(sdkey, 24, 1) = 'V' and substr(sddaten, 1, 1) = 'A' "
                    + " and substr(sdkey, 3, 3) = D.VAAARTGRP) as ARTNAME, "
                    + " A.VKPWUTERM,A.VKPLIEFTERM,A.VKPVERZTERM1,A.VKPVERZTERM2,A.VKPVERZTERM3,A.VKPVERZTERM4, "
                    + " E.VAFARTBEZ AS STAFF,TRIM(C.HRNNAME) AS SACHB, "
                    + " (CASE WHEN A.VKPVERZTERM3>A.VKPLIEFTERM THEN 'DELAY' WHEN A.VKPLIEFTERM = 0 "
                    + " THEN 'SCHEDULE' ELSE 'NORMAL' END) AS STATUS,A.VKPERL, "
                    + " (SELECT STRAGG(trim(TXTKTEXT))  "
                    + " FROM TXTIND X, TXTKON Y WHERE X.TXTIMANDANT = Y.TXTKMANDANT "
                    + " AND X.TXTITXTART = 'VKT' AND X.TXTIPIND = Y.TXTKPIND AND X.TXTISIND LIKE '%IVVT%' "
                    + " AND SUBSTR(X.TXTISIND,1,11) = A.VKPAUFKRS||A.VKPAUFLFD||A.VKPAUFPOS) AS REMARK "
                    + " FROM VKPOS A,KDSTAMM B,HRSTAMM C,VAART D,VAFARBEN E  "
                    + " WHERE A.VKPMANDANT = B.KDMANDANT AND A.VKPKUNDNR = B.KDKUNDNR "
                    + " AND A.VKPMANDANT = C.HRMANDANT AND A.VKPSACHBE = C.HRBENID "
                    + " AND A.VKPMANDANT = D.VAAMANDANT AND A.VKPKOLL = D.VAAKOLL "
                    + " AND A.VKPART = D.VAAART AND A.VKPSA <>'AJ' "
                    + " AND A.VKPMANDANT = E.VAFMANDANT AND A.VKPKOLL = E.VAFKOLL "
                    + " AND A.VKPART = E.VAFART AND A.VKPFARBE = E.VAFFARBE  "
                    + " AND (A.VKPAUFKRS LIKE 'H%' OR A.VKPAUFKRS LIKE 'Y%') "
                    + " AND (A.VKPNEUDAT >= " + FROMDNDATE + " AND  A.VKPNEUDAT <= " + ENDDNDATE + ")) "
                    + " WHERE DESCR LIKE '%" + DESCR + "%' "
                    + " ORDER BY VKPNEUDAT,VKPAUFKRS,ORDERNO,ITEM  ";

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["TYPE"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["TYPE"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["CUNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["CUNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DESCR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["DESCR"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ARTNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ARTNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["STAFF"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["STAFF"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["SACHB"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["SACHB"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["REMARK"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["REMARK"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }       
        public DataSet BuildJLTVK02YFData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetVK02(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}